package com.hp.attendance.dao;

import com.hp.attendance.entity.Department;
import com.hp.attendance.entity.Employee;
import com.hp.attendance.entity.Overtime;
import com.hp.attendance.entity.dto.DepOvertimeDto;
import com.hp.attendance.util.BaseDAO;

import java.io.IOException;
import java.sql.SQLException;
import java.util.List;

public class OvertimeDao extends BaseDAO {


    /**
     * 查询所有加班的列表
     * @return
     * @throws SQLException
     */
    public List<Overtime> getList() throws SQLException, IOException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        String sql ="select * from overtime";
        List<Overtime> list = queryList(sql, new Object[0], new Overtime());
        return list;
    }

    /**
     * 查询所有部门的列表
     * @return
     * @throws SQLException
     */
    public List<Department> getDepList() throws SQLException, IOException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        String sql ="select * from department";
        List<Department> list = queryList(sql, new Object[0], new Department());
        return list;
    }
    /**
     * 查询所有员工的列表
     * @return
     * @throws SQLException
     */
    public List<Employee> getEmpList() throws SQLException, IOException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        String sql ="select * from employee";
        List<Employee> list = queryList(sql, new Object[0], new Employee());
        return list;
    }
    /**
     * 分页查询记录
     * @param pageNo 当前页
     * @param pageSize 每页显示的条数
     * @return
     */
    public List< DepOvertimeDto> getLimitList(int pageNo, int pageSize) throws SQLException, IOException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        String sql ="select o.id id,o.day day,d.`name` depname,e.`name` empname from overtime o "+
        " left join department d on d.department_number = o.department_number "+
        " LEFT JOIN employee e on e.employee_number = o.employee_number "+
                " limit ?,?";
        Object[] obj ={(pageNo-1)*pageSize,pageSize};
        List< DepOvertimeDto>  depOvertimeDto = queryList(sql, obj, new DepOvertimeDto());
        return depOvertimeDto;
    }

    /**
     * 查询总记录数
     * @return
     * @throws SQLException
     */
    public long getTotal() throws SQLException, IOException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        String sql ="select count(1) from overtime o "+
                " left join department d on d.department_number = o.department_number "+
                " LEFT JOIN employee e on e.employee_number = o.employee_number ";
        int count = queryCount(sql, new Object[0]);
        return count;
    }
    /**
     * 修改加班
     * @param overtime
     */
    public void updateOvertime(Overtime overtime) throws SQLException, IOException, ClassNotFoundException {
        String sql ="update overtime set day=?,department_number=? ,employee_number=? where id=?";
        Object[] obj={overtime.getDay(),overtime.getDepartment_number(),overtime.getEmployee_number(),overtime.getId()};
        addOrUpdOrDel(sql,obj);
    }
    /**
     * 删除角色
     * @param id
     */
    public void delOvertime(int id) throws SQLException, IOException, ClassNotFoundException {
        String sql ="delete from overtime where id=?";
        Object[] obj ={id};
        addOrUpdOrDel(sql,obj);
    }

    /**
     * 添加加班
     * @param overtime
     * @throws SQLException
     * @throws IOException
     * @throws ClassNotFoundException
     */
    public void addOvertime(Overtime overtime) throws SQLException, IOException, ClassNotFoundException {

        String sql ="insert into overtime values(null,?,?,?,null,null,null)";
        Object[] obj={overtime.getDepartment_number(),overtime.getEmployee_number(),overtime.getDay()};
        addOrUpdOrDel(sql,obj);
    }

    /**
     *通过部门名称查找对应加班列表
     */
    public Overtime getOvertimeByDep(String depname) throws SQLException, IOException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        String sql="select o.* from overtime o " +
                " left join department d on d.department_number = o.department_number " +
                " where d.name=?";
        Object[] obj ={depname};
        Overtime overtime = queryOne(sql, obj, new Overtime());
        return  overtime;
    }
    /**
     *通过员工名称查找对应加班列表
     */
    public Overtime getOvertimeByEmp(String empname) throws SQLException, IOException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        String sql="select o.* from overtime o " +
                "  LEFT JOIN employee e on e.employee_number = o.employee_number " +
                " where e.name=?";
        Object[] obj ={empname};
        Overtime overtime = queryOne(sql, obj, new Overtime());
        return  overtime;
    }

    /**
     * 根据id查询
     * @param
     * @return
     */
    public Overtime getOvertime(int id) throws SQLException, IOException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        String sql ="select * from overtime where id=?";
        Object[] obj = {id};
        Overtime overtime = queryOne(sql, obj, new Overtime());
        return overtime;
    }
    /**
     * 查询员工的列表通过部门号
     * @return
     * @throws SQLException
     */
    public List<Employee> getEmpListByDep(int dep_num) throws SQLException, IOException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        String sql ="select e.* from overtime o "+
                " left join department d on d.department_number = o.department_number "+
                " LEFT JOIN employee e on e.employee_number = o.employee_number where d.department_number=?";
        Object[] obj = {dep_num};
        List<Employee> list = queryList(sql,obj, new Employee());
        return list;
    }
}
